﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MyFrameWork.Dam;
using System.Data;
using System.Data.Common;
using MyFrameWork.Common;
using MvcApplication5.Models;

namespace MvcApplication5.DAM
{
    public class StaffDAM : DataDAM
    {
        /// <summary>
        /// 创建连接数据库
        /// </summary>
        public StaffDAM()
        {
            this.CreateDataBase("MAINDB");
        }
        /// <summary>
        /// 获取员工信息表中的所有匹配数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetStaffListByPositionId(Dictionary<String, String> searchCondition, Pager pager)
        {
            // 获取记录数
            pager.RecordCount = this.CountRecord(searchCondition);

            // 没有检索到数据的场合，处理终止
            if (pager.RecordCount == 0)
            {
                return new DataSet();
            }
            // 生成sqlSelect命令
            DbCommand command = GetEmptyCommand();

            // 如果排序字段为空，则默认排序字段为“create_date”
            string sortColumn = pager.SortColumn;
            string sortMode = pager.SortMode;
            if (string.IsNullOrEmpty(sortColumn))
            {
                sortColumn = "staff_id";
                sortMode = "ASC";
            }

            string strSQL = String.Format(@"
                            SELECT
                                TB_staff.*
                            FROM
                                TB_staff 
                            INNER JOIN 
                                TB_position ON TB_staff.position_id=TB_position.position_id
                            WHERE 
                                TB_staff.record_status = 0 
                                {0}", this.GetSqlWhere(searchCondition));

            string sql = @" SELECT * 
                            FROM
                            (
                                SELECT 
                                    ROW_NUMBER() OVER(ORDER BY {3} {4}) AS rowNum, 
                                    *
                                FROM ({0}) AS T1 
                            ) AS T WHERE rowNum >= {1} AND rowNum <= {2} ORDER BY {3} {4}";

            sql = string.Format(sql, strSQL, pager.BeginRow, pager.EndRow,
                sortColumn, sortMode);


            // 重置CommandText
            command.CommandText = sql;

            //SQL文を実行する
            return ExecuteDataSet(command, null);
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="condition">条件集合</param>
        /// <returns>记录总数</returns>
        private int CountRecord(Dictionary<String, String> condition)
        {
            // 生成DB命令
            DbCommand command = GetEmptyCommand();

            // 设定获取记录总数的sqlSelect
            string sqlSelect = String.Format(@"
                            SELECT 
                               COUNT(*) 
                             FROM
                                TB_staff 
                            INNER JOIN 
                                TB_position ON TB_staff.position_id=TB_position.position_id
                            WHERE 
                                TB_staff.record_status = 0 
                               {0}",
               this.GetSqlWhere(condition));

            // 重置CommandText
            command.CommandText = sqlSelect;

            // 检索数据
            object retObj = ExecuteScalar(command);

            // 返回数据
            return retObj == null ? 0 : Int32.Parse(retObj.ToString());
        }
        /// <summary>
        /// 获取sqlwheres条件
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private string GetSqlWhere(Dictionary<String, String> condition)
        {
            string sqlWhere = string.Empty;
            if (condition.ContainsKey("position_id") && !string.IsNullOrEmpty(condition["position_id"].ToString()))
            {
                sqlWhere += string.Format(" AND TB_staff.position_id = '{0}' ", condition["position_id"]);
            }
            if (condition.ContainsKey("staff_code") && !string.IsNullOrEmpty(condition["staff_code"].ToString()))
            {
                sqlWhere += string.Format(" AND TB_staff.staff_code like '%{0}%' ", condition["staff_code"]);
            }
            if (condition.ContainsKey("staff_name") && !string.IsNullOrEmpty(condition["staff_name"].ToString()))
            {
                sqlWhere += string.Format(" AND TB_staff.staff_name like  '%{0}%' ", condition["staff_name"]);
            }
            return sqlWhere;
        }
        /// <summary>
        /// 获取员工信息表中的相应id的数据
        /// </summary>
        /// <param name="employee_id"></param>
        /// <returns></returns>
        public DataSet GetStaffInfoById(string staff_id)
        {
            try
            {
                ConditionList conditionList = new ConditionList(ConditionList.ConditionType.AND);

                conditionList.Add("staff_id", ConditionList.EquipType.EQUIP, staff_id);

                conditionList.Add("record_status", ConditionList.EquipType.EQUIP, "0");

                return this.SelectData("TB_staff", "*", conditionList, null);
            }
            catch (Exception ex)
            {
                return null;
            }

        }
        /// <summary>
        /// 新规员工信息
        /// </summary>
        /// <param name="model">员工model</param>
        /// <returns>受影响行数</returns>
        public int SaveStaffInfo(StaffModel model)
        {
            try
            {
               // this.SetRejectList(StaffModel.rejectColumn);

                return this.InsertData("TB_staff", "staff_id", model, null);
            }
            catch
            {
                return -1;
            }
        }
        /// <summary>
        /// 修改员工信息
        /// </summary>
        /// <param name="model">员工model</param>
        /// <returns>受影响行数</returns>
        public int UpdateStaffInfo(StaffModel model)
        {
            try
            {
                //this.SetRejectList(StaffModel.rejectColumn);

                ConditionList conditionList = new ConditionList(ConditionList.ConditionType.AND);

                conditionList.Add("staff_id", ConditionList.EquipType.EQUIP, model.staff_id.ToString());

                Dictionary<string, string> targetList = new Dictionary<string, string>();

                targetList.Add("staff_name", model.staff_name);
                targetList.Add("staff_code", model.staff_code);
                targetList.Add("position_id", model.position_id.ToString());
                if (!string.IsNullOrEmpty(model.staff_password))
                {
                    targetList.Add("staff_password", model.staff_password);
                }
                targetList.Add("birth_date", model.birth_date.ToString());

                return this.UpdateData("TB_staff", targetList, conditionList, null);
            }
            catch (Exception)
            {

                return -1;
            }
        }
        /// <summary>
        /// 禁用员工信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteStaffInfo(List<string> staff_ids)
        {
            try
            {
                //this.SetRejectList(StaffModel.rejectColumn);

                ConditionList conditionList = new ConditionList(ConditionList.ConditionType.AND);

                conditionList.Add("staff_id", ConditionList.EquipType.IN, CommonUtility.ConvertListToString(staff_ids));

                Dictionary<string, string> targetList = new Dictionary<string, string>();

                targetList.Add("record_status", "1");

                return this.UpdateData("TB_staff", targetList, conditionList, null);
            }
            catch (Exception)
            {

                return -1;
            }
        }
    }
}